package com.cpi.cornpp.creditline.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.creditline.dto.CreditLineDTO;
import com.cpi.cornpp.creditline.exception.CreditLineException;
import com.cpi.cornpp.plants.exception.PlantException;
import com.cs.common.pool.DBDAO;

/**
 * 
 * @description: This Class is used to handle the Credit Line Data Access
 * @author Edgar Joao
 */
public class CreditLineDAO extends DBDAO{	
	/** Datasource name*/
	private static final String DATASOURCE 	= "cornpp";//$NON-NLS-1$
	/** SQL Credit Line */ 
	private static final String SQL_CREDIT_LINE = "SELECT * FROM credits_lines";//$NON-NLS-1$
	/** SQL Get Credit Line */ 
	private static final String SQL_GET_CREDIT_LINE = "SELECT credit_line_id, description, amount, out_of_credit*100, round( tax1*100, 2 ), round( tax2*100, 2) FROM credits_lines";//$NON-NLS-1$
	/** SQL Insert Credit Line */ 
	private static final String SQL_INSERT_CREDIT_LINE = "INSERT INTO credits_lines (credit_line_id, description, amount, out_of_credit, tax1, tax2) VALUES (?,?,?,?,?,?)";//$NON-NLS-1$
	/** SQL Update Credit Line */ 
	private static final String SQL_UPDATE_CREDIT_LINE = "UPDATE credits_lines SET description = ?, amount = ?, out_of_credit = ?, tax1 = ?, tax2 = ? WHERE credit_line_id = ?";//$NON-NLS-1$
	
	
	/**
	 * Exist Credit Line
	 * @param entity
	 * @return
	 * @throws Exception
	 */
	public boolean existCreditLine() throws Exception {
		PreparedStatement preparedStatement =  null;
		boolean result = false;		
		try {
			preparedStatement =  prepareSQL(SQL_CREDIT_LINE, DATASOURCE);														
			ResultSet rst = executeQuery(preparedStatement);
			if (rst.next()) {				
				result=true;							
			}else{
				result=false;
			}
			
		}
		catch (SQLException sqlException){
			CreditLineException creditLineException = null;
			sqlException.printStackTrace(System.out);
			creditLineException = new CreditLineException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw creditLineException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return result;
	}
	
	
	/**
	 * Insert Credit Line
	 * @param entity
	 * @return
	 * @throws Exception
	 */
	public void addCreditLine(CreditLineDTO entity) throws Exception {
		PreparedStatement preparedStatement =  null;		
		int exInsert = 0;
		try {
			preparedStatement =  prepareSQL(SQL_INSERT_CREDIT_LINE, DATASOURCE);			
			preparedStatement.setInt(1, entity.getCreditLineId());
			preparedStatement.setString(2, entity.getDescription());
			preparedStatement.setDouble(3, entity.getLimit());
			preparedStatement.setDouble(4, (entity.getOutOfCredit()/100));
			preparedStatement.setDouble(5, (entity.getTax1()/100));
			preparedStatement.setDouble(6, (entity.getTax2()/100));
			
			exInsert = executeInsert(preparedStatement);
			if (exInsert <= 0 ) {
				CreditLineException creditLineException = null;
				creditLineException = new CreditLineException (MSG_NO_INSERT,CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);				
				throw creditLineException;				
			}		
		}
		catch (SQLException sqlException){
			CreditLineException creditLineException = null;
			sqlException.printStackTrace(System.out);
			creditLineException = new CreditLineException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw creditLineException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}		
	}
	
	
	/**
	 * Update Credit Line
	 * @param entity
	 * @return
	 * @throws Exception
	 */
	public void updateCreditLine(CreditLineDTO entity) throws Exception {
		PreparedStatement preparedStatement =  null;		
		int exDelete = 0;
		try {
			preparedStatement =  prepareSQL(SQL_UPDATE_CREDIT_LINE, DATASOURCE);						
			preparedStatement.setString(1, entity.getDescription());
			preparedStatement.setDouble(2, entity.getLimit());
			preparedStatement.setDouble(3, (entity.getOutOfCredit()/100));
			preparedStatement.setDouble(4, (entity.getTax1()/100));
			preparedStatement.setDouble(5, (entity.getTax2()/100));
			preparedStatement.setInt(6, entity.getCreditLineId());
			
			exDelete = executeInsert(preparedStatement);
			if (exDelete <= 0 ) {
				PlantException plantException = null;
				plantException = new PlantException (MSG_NO_UPDATE,CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);				
				throw plantException;				
			}		
		}
		catch (SQLException sqlException){
			PlantException plantException = null;
			sqlException.printStackTrace(System.out);
			plantException = new PlantException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw plantException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}		
	}
	
	/**
	 * Get a Credit Line DTO
	 * @author Edgar Joao
	 * @return
	 * @throws Exception
	 */	
	public CreditLineDTO getCredtiLineDTO()throws Exception{		
		PreparedStatement preparedStatement =  null;
		CreditLineDTO creditLineDTO = null;
		
		try {							
			preparedStatement = prepareSQL(SQL_GET_CREDIT_LINE, DATASOURCE);			
			ResultSet rst = executeQuery(preparedStatement);
			if (rst.next()){
				creditLineDTO = new CreditLineDTO();				
				creditLineDTO.setCreditLineId(rst.getInt(1));
				creditLineDTO.setDescription(rst.getString(2));
				creditLineDTO.setLimit(rst.getDouble(3));
				creditLineDTO.setOutOfCredit((rst.getDouble(4)));
				creditLineDTO.setTax1(rst.getDouble(5));
				creditLineDTO.setTax2((rst.getDouble(6)));
			}
		}
		catch (SQLException sqlException){
			CreditLineException crediLineException = null;
			sqlException.printStackTrace(System.out);
			crediLineException= new CreditLineException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw crediLineException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return creditLineDTO;
	}
	
}
